package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.StateDay;

@Repository("stateDayDao")
public class StateDayDaoImpl extends TemplateDaoImpl<StateDay, Integer>implements StateDayDao{
	/**
	 * 根据设备id，设备状态，归属日期查询设备每天的状态记录
	 * @param equId
	 * @param stateType
	 * @param belongDate
	 * @return
	 */
	@Override
	public StateDay getStateDay(int equId, String stateType, String belongDate) {
		String sql="select * from stateDay where equId=:equId and stateType=:stateType and CONVERT(VARCHAR(10),belongDate,120)=:belongDate";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("belongDate", belongDate);
		return jt.queryForObject(sql.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(StateDay.class));
	}
	/**
	 * 根据不同时间段查询设备每天状态记录
	 * @param equId
	 * @param stateType
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Override
	public List<StateDay> getStateDays(int equId, String stateType,String startDate, String endDate) {
		String  sql="select * from stateDay where equId=:equId and stateType=:stateType and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateDay.class));
	}
	
	/***
	 * 获取时间段内的工作总时长
	 * @param equId
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Override
	public Double getTotalWorkTime(int equId, String startDate, String endDate,String stateType) {
		Map<String, Object> params=new HashMap<String, Object>();
		StringBuilder sql = new StringBuilder("select COALESCE(SUM(duration),0) from stateDay where equId=:equId and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate ");
		if(stateType==null||stateType.equals("")){
			sql.append(" and stateType<>'停机' ");
		}else{
			sql.append(" and stateType=:stateType ");
			params.put("stateType", stateType);
		}
		params.put("equId", equId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sql.toString(), params, Double.class);
	}
	@Override
	public List<StateDay> getStateDayByDate(int equId, String belongDate) {
		StringBuilder sql = new StringBuilder("select * from stateDay where equId=:equId  and CONVERT(VARCHAR(10),belongDate,120)=:belongDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("belongDate", belongDate);
		return jt.query(sql.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(StateDay.class));
	}
	@Override
	public List<StateDay> getDistinctType(int equId, String startDate,
			String endDate) {

		StringBuilder sql = new StringBuilder("select distinct(stateType) from stateDay where equId=:equId  and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.query(sql.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(StateDay.class));
	
	}
}
